/*--------------------------按照采样单位插入数据--------------------------*/
create or replace procedure init_IGDJC_B0(
)
as
	userid_ varchar2(8);
	i int;
begin
	i:=0;
	for i in 1..dim_xzqh.count loop
		userid_:=
		--插入2011年4个季度的数据
		exec insert_IGDJC_B0(to_date('2011-01-01','yyyy-mm-dd'),userid_);
		exec insert_IGDJC_B0(to_date('2011-04-01','yyyy-mm-dd'),userid_);
		exec insert_IGDJC_B0(to_date('2011-07-01','yyyy-mm-dd'),userid_);
		exec insert_IGDJC_B0(to_date('2011-10-01','yyyy-mm-dd'),userid_);
		--插入2012年4个季度的数据
		exec insert_IGDJC_B0(to_date('2012-01-01','yyyy-mm-dd'),userid_);
		exec insert_IGDJC_B0(to_date('2012-04-01','yyyy-mm-dd'),userid_);
		exec insert_IGDJC_B0(to_date('2012-07-01','yyyy-mm-dd'),userid_);
		exec insert_IGDJC_B0(to_date('2012-10-01','yyyy-mm-dd'),userid_);
	end loop;
end insert_IGDJC_B0;

/*--------------------------随机生成数据--------------------------*/
CREATE OR REPLACE 
procedure insert_IGDJC_B0(
BBQ_begindate date,--季度开始日期(用于计算季度,生成结果产生日期/生产日期/采样日期)
userid varchar--采样单位
)
as  
  BBQ_char varchar2(8);--季度
  s2_date date;--结果产生日期
  f2_date date;--生产日期
  c2_date date;--采样日期
  r2_num int;--合格数量
  g2_num int;--采样数量
  q2_num int;--送检数量
  d2_name varchar2(20);--采样地区
  i2_id int;--食品种类
  j2_id varchar2(3);--检测项目
  k2_id varchar2(10);--样品产地
  l2_id varchar2(10);--生产企业
  m2_id varchar2(10);--企业类型
  n2_char varchar2(10);--企业联系方式
  o2_name varchar2(10);--注册商标
  for_num int;--循环次数
  i int;
  type temp_array is varray(18) of varchar2(40);--定义数组
  e2_array temp_array;
  j2_array temp_array;
begin
  --自定义的样品名称
  e2_array:=temp_array('白菜','猪肉','扇贝','米粉','鲫鱼','薯片','紫菜','牛奶','方便面','花生酱','开心果','婴幼儿谷类辅助食品','小麦','新鲜蔬菜','豆腐','腌腊肉','粉丝','果冻');  
  --食物种类对应的检测项目
  j2_array:=temp_array('101','101','101','102','103','104','104','201','202','301','301','301','302','401','501','503','505','506');  
  --计算BBQ
  BBQ_char:=to_char(BBQ_begindate,'yyyy')||'0'||to_char(BBQ_begindate,'Q')||'--';
  --采样地区
  select CAPTION_ into d2_name from dim_xzqh where ID_=userid;
  --随机生成插入数据的条数
  for_num:=TRUNC(DBMS_RANDOM.VALUE(15,30));
  i:=0;
  for i in 1..for_num loop
    --随机生成日期
    f2_date:=BBQ_begindate+TRUNC(DBMS_RANDOM.VALUE(1,20));
    c2_date:=f2_date+TRUNC(DBMS_RANDOM.VALUE(1,20));
    s2_date:=s2_date+TRUNC(DBMS_RANDOM.VALUE(1,20));
    --随机生成数量
    g2_num:=TRUNC(DBMS_RANDOM.VALUE(200,500));
    q2_num:=g2_num-TRUNC(DBMS_RANDOM.VALUE(0,(g2_num+1)));
    r2_num:=TRUNC(DBMS_RANDOM.VALUE(0,(q2_num+1)));
    --随机生成食物种类
    i2_id:=TRUNC(DBMS_RANDOM.VALUE(1,19));
	--根据食物种类计算检测项目
    j2_id:= j2_array(i2_id); 
    if i2_id = 14 then
      j2_id:= CONCAT('40',TRUNC(DBMS_RANDOM.VALUE(1,4)));
    elsif i2_id = 15 then 
      j2_id:= CONCAT('50',TRUNC(DBMS_RANDOM.VALUE(1,3)));
    elsif i2_id = 16 then 
      j2_id:= CONCAT('50',TRUNC(DBMS_RANDOM.VALUE(3,5)));
    end if;
    --样品产地
    select ID_ into k2_id from (select * from dim_ypcd order by dbms_random.value ) where rownum <2;
    --生产企业信息
	select ID_ into l2_id from (select * from dim_scqyml order by dbms_random.value ) where rownum <2;
    select ID_ into m2_id from (select * from dim_qylx order by dbms_random.value ) where rownum <2;
    n2_char:=to_char(TRUNC(DBMS_RANDOM.VALUE(50000000,89999999)));
    o2_name:=l2_id;  
     
    --插入数据
    insert into IGDJC_B0(BBQ_,USERID_,BTYPE_,A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,SHTAG_,SHTAG2_,OPTION_,ISHJ_) values(BBQ_char,userid,0,i,temp_sequence_b2.nextval,to_char(c2_date,'yyyymmdd'),d2_name,e2_array(i2_id),to_char(f2_date,'yyyymmdd'),g2_num,TRUNC(DBMS_RANDOM.VALUE(1,3)),to_char((i2_id),'FM09'),j2_id,k2_id,l2_id,m2_id,n2_char,o2_name,null,q2_num,r2_num,to_char(s2_date,'yyyymmdd'),userid,'0',null,null,'0');
  end loop;
end insert_IGDJC_B0;

/*--------------------------调整生成的随机数据--------------------------*/
﻿--随机10条记录,将送检数量和合格数量设置为0
update igdjc_b0 set q2=0,r2=0 where db2index_ in (select db2index_ from (select * from igdjc_b0 order by dbms_random.value) where rownum <11);

select substr(userid_,1,4),sum(r2)/sum(q2) from igdjc_b0
where bbq_ like '201201%' 
group by substr(userid_,1,4)
order by sum(r2)/sum(q2) asc

update igdjc_b0 set r2=q2*(TRUNC(DBMS_RANDOM.VALUE(80,95))/100) 
where bbq_ like '201201%' 
and userid_ like '4401%';